const Router = require('koa-router');
const router = new Router();
const {db, Op, QueryTypes} = require("../../models/db");
const tools = require("../../tools/tool");
const tool = require("../../tools/tool");


// 下拉数据
router.post('/common/dropdown', async ctx => {
    try {
        let body = ctx.request.body
        let user = ctx.user
        let buId = body.buId ? body.buId : 0;
        let invId = body.invId ? body.invId : 0;
        let tenantId = user? user.tenantId:'';
        let cat = ctx.request.body.cat
        let keyWord = tools.xe.trim(ctx.request.body.keyWord)
        let id = ctx.request.body.id?ctx.request.body.id:''
        let sql = ''
        if(cat === 'unit') {
            if(id) {
                let list = await db.query(`select name from unit where name='${id}'`,{type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
            else {
                let list = await db.query(`select name as id,name from unit order by sort`,{type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
        }
        if(cat === 'hj') {
            if(id) {
                let list = await db.query(`select id,name from goods_hj where tenantId=${tenantId} and id=${id} `,{type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
            else {
                let list = await db.query(`select id,name from goods_hj where tenantId=${tenantId}  order by sort`,{type: QueryTypes.SELECT})
                return ctx.suc('', list)
            }
        }
        if (cat === 'dept') {
            sql = `select id,name  from dept where tenantId=${tenantId} `
            if(keyWord) {
                sql += ` and (b.name like '${keyWord}' or b.pinYin like '${keyWord}') `
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'sales') {
            sql = `select id,name from admin where tenantId=${tenantId} `
            let auth = tools.authSql(ctx)
            if(auth) {
                sql += ` and id in (${auth}) `
            }
            if(keyWord) {
                sql += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%') `
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'srSubject') {
            // 收入科目
            if(ctx.user.isCard === 0) {
                sql = `select id,code,concat(code,name) as name from category where tenantId=${tenantId} and typeNumber='subject' and pid in(select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务收入')`
            }
            else {
                sql = `
                select id,code,concat(code,name) as name from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'营业外收入'  
                select id, code,concat(code,N'_其他业务收入_',name) as name from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0 
                and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务收入'  ) `;
            }
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%' or pinYin like '%${keyWord}%') `;
            }
            sql += " order by code asc";
            let lists = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', lists);
        }
        if (cat === 'zcSubject') {
            // 支出科目
            let sql;
            if(ctx.user.isCard === 0) {
                sql = `select id,code,concat(code,name) as name from category where tenantId=${tenantId} and typeNumber='subject' and pid in(select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务支出')`
            }
            else {
                sql = `SELECT * from ( 
                select id,code,concat(code,name) name,pinYin,isDel from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'营业外支出' union all  
                select id, code, concat(code,N'_销售费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'销售费用'  ) union all  
                select id, code, concat(code,N'_管理费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'管理费用'  ) union all   
                select id, code, concat(code,N'_财务费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'财务费用'  )  
                UNION ALL  select id, code, concat(code,N'_其他业务支出_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务支出'  )  
                 ) a where isDel=0 `
            }
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%' or pinYin like '%${keyWord}%') `;
            }
            sql += " order by code asc";
            let lists = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', lists);
        }
        if (cat === 'deliver') {
            sql = `select a.id,a.name from admin a inner join role b on a.roleId=b.id and a.tenantId=${tenantId} and b.name in(N'送货员',N'驾驶员') `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'billState') {
            let ret = [
                {id: 'draft', name: '草稿'},
                {id: 'accounted', name: '已审核'},
                {id: 'del', name: '已删除'},
                {id: 'all', name: '全部'},
            ]
            if(id) {
                ret = ret.filter(row => {
                    return row.id === id
                })
            }
            return ctx.suc('', ret)
        }
        if (cat === 'provider') {
            let where = {
                tenantId: tenantId,
                isProvider: 1,
                isDel: 0
            }
            if(id) {
                where.id = id
                let ret = await db.models['contact'].findOne(where)
                return ctx.suc('', ret)
            }
            else {
                if(keyWord) {
                    where[Op.or] = {
                        name: {
                            [Op.like]: `%${keyWord}%`
                        },
                        pinYin: {
                            [Op.like]: `%${keyWord}%`
                        }
                    }
                }
                let opt = {
                    where: where,
                    attributes: ['id', 'name', 'address']
                }
                if(!keyWord) {
                    opt.limit = 30
                }
                let ret = await db.models['contact'].findAll(opt)
                let arr = []
                ret.map(row => {
                    arr.push({
                        id: row.id,
                        name: row.name,
                        suffix: row.address
                    })
                })
                return ctx.suc('', arr)
            }
        }
        if (cat === 'customer') {
            let where = {
                tenantId: tenantId,
                isCustomer: 1,
                isDel: 0
            }
            if(id) {
                where.id = id
                let ret = await db.models['contact'].findOne({
                    where
                })
                return ctx.suc('', ret)
            }
            else {
                if(keyWord) {
                    where[Op.or] = {
                        name: {
                            [Op.like]: `%${keyWord}%`
                        },
                        pinYin: {
                            [Op.like]: `%${keyWord}%`
                        }
                    }
                }
                let opt = {
                    where: where,
                    attributes: ['id', 'name','linkMobile','linkPhone', 'linkName', 'address','printBar']
                }
                if(!keyWord) {
                    opt.limit = 30
                }
                let ret = await db.models['contact'].findAll(opt)
                let arr = []
                ret.map(row => {
                    arr.push({
                        id: row.id,
                        name: row.name,
                        suffix: row.address,
                        linkMobile: row.linkMobile?row.linkMobile:(row.linkPhone?row.linkPhone:''),
                        linkName: row.linkName,
                        address: row.address,
                        printBar: row.printBar,
                    })
                })
                return ctx.suc('', arr)
            }
        }
        if (cat === 'contactList') {
            // 下拉商品 不带库存
            sql = `select top 80 id,code,name,linkName,linkMobile,address,isnull(linkPhone,'') linkPhone from contact where tenantId=${tenantId} `
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%' or code like '%${keyWord}%' or linkMobile like '%${keyWord}%' or linkName like '%${keyWord}%' )`;
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            let arr = []
            ret.map(row => {
                arr.push({
                    id: row.id,
                    name: row.name,
                    suffix: row.address,
                    linkMobile: row.linkMobile?row.linkMobile:(row.linkPhone?row.linkPhone:''),
                    linkName: row.linkName,
                    address: row.address,
                })
            })
            return ctx.suc('', arr)
        }
        if (cat === 'send') {
            let qty = tools.xe.toNumber(body.qty)
            // if (!buId) return ctx.err('请选择客户');
            // if (!invId)  return ctx.err('请选择商品');
            let bu = buId ? ` and a.buId=${buId} ` : ''
            let inv = invId ? ` and a.invId=${invId} ` : ''
            let s = qty > 0 ? `select distinct cast (a.id as varchar(10)) as id,concat(N'赠【',iif(goodName=sendGoodName,N'本品',sendGoodName),N'】',cast(sendq as varchar(10)),
                N' (买',buyQty,N'赠',sendQty,sendUnit,')') name from (
                    select top 10  a.*,b.name as goodName,
                 c.name as sendGoodName,cast((sendQty*${qty})*1.0/buyQty as decimal(10,1)) sendq from send as a inner join v_goods as b on a.isDel=0 and a.invId=b.id 
                     inner join v_goods as c on a.sendInvId=c.id 
                   where a.tenantId=${tenantId} ${inv} ${bu} order by a.id desc) a  
                   union  all` : ''
            // 赠品下拉
            sql = ` ${s} 
                   select id,name 
                   from 
                   (
                       select N'代赠' id,  N'代赠' name union  all
                       select N'自赠' id,  N'自赠' name union  all
                       select N'陈列' id,  N'陈列' name union  all 
                     select N'销量' id,  N'销量' name union  all 
                     select N'合同' id,  N'合同' name union  all 
                     select N'开业' id,  N'开业' name union  all 
                     select N'赠饮' id,  N'赠饮' name 
                   ) a `;
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'goodName') {
            // 下拉商品 不带库存
            sql = `select id,name,spec,unit1,unit2,price2   from v_goods where tenantId=${tenantId}  and isDel=0 `
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or  pinYin like '%${keyWord}%') `;
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'subject') {
            sql =  `select id,pid,name,code,fullName,direct from category where tenantId=${tenantId} and typeNumber='subject' and isDel=0`;
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%' or pinYin like '%${keyWord}%' ) `;
            }
            sql += " order by code ";
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', tool.toArrayTree(ret))
        }
        if (cat === 'menu') {
            sql =  `select id,pid,name from menu_v5 `;
            sql += " order by id ";
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', tool.toArrayTree(ret))
        }
        // 销售明细表票据类型
        if (cat === 'billCat') {
            let cat = [{
                id: 150601,
                name: '销售单'
            },
                {
                    id: 150602,
                    name: '销售退货单'
                },
                {
                    id: 150603,
                    name: '销售换货单'
                },
            ]
            return ctx.suc('', cat);
        }
        if (cat === 'selType') {
            let cat = [{
                id: 1,
                name: '销售明细'
            },
                {
                    id: 2,
                    name: '销售业绩明细'
                },
            ]
            return ctx.suc('', cat);
        }
        if (cat === 'invName') {
            sql = `with bills as (
                select tenantId,invId,sum(lastCount) qty from inventory where tenantId=${tenantId} GROUP BY tenantId,invId
            ) select top 30 a.id,a.tenantId,a.name,a.spec,a.unit1,a.unit2,a.price1,a.price2,  dbo.setAssQty(isnull(b.qty,0),a.spec,a.unit1,a.unit2) leftQty 
              from v_goods a left join bills b on a.tenantId=b.tenantId  and a.id=b.invId 
                where a.tenantId=${tenantId} 
            `
            if(keyWord) {
                sql += ` and (a.name like '%${keyWord}%' or a.pinYin like '%${keyWord}%' or a.barCode like '%${keyWord}%' or a.barCode1 like '%${keyWord}%') `
            }
                sql += `  order by a.categoryId, a.name `
                let ret = await db.query(sql, {type: QueryTypes.SELECT})
                ret.map(row => {
                    row.suffix = tools.xe.toNumber(row.leftQty) === 0 ? '' : row.leftQty
                })
                return ctx.suc('', ret)
        }
        if (cat === 'deliverList') {
            // 下拉商品 带库存
            sql = `SELECT id,name from admin where roleId=(select id from role where tenantId=${tenantId} and name=N'送货员') and isDel=0 `;
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%')`;
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'salesList') {
            sql = `SELECT id,name from admin where roleId=(select id from role where tenantId=${tenantId} and name=N'销售员') and isDel=0 `
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%')`
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'unitName') {

            if (!invId) {
                return ctx.err('没有选择商品');
            }
            let data = await db.models['vGoods'].findOne({
                where: {
                    id: invId
                }
            })
            let u1 = data.unit1;
            let u2 = data.unit2 ? data.unit2 : '';
            let spec = data.spec;
            if (data.unit1 === data.unit2 || data.unit2 === '') {
                return ctx.suc('', [
                    {id: u1, name: u1, calNum: spec}
                ])
            } else {
                return ctx.suc('',[
                    {id: u1, name: u1, calNum: spec},
                    {id: u2, name: u2, calNum: 1},
                ])
            }
        }
        if (cat === 'transType') {
            return ctx.suc('', [
                {id: 150501, name: '购货单'},
                {id: 150502, name: '购货退货单'},
                {id: 150601, name: '销售单'},
                {id: 150602, name: '销售退货单'},
                {id: 150603, name: '销售换货单'},
                {id: 150706, name: '其他入库单'},
                {id: 150806, name: '其他出库单'},
                {id: 153001, name: '收款单'},
                {id: 153101, name: '付款单'},
                {id: 153401, name: '通用收入单'},
                {id: 153402, name: '通用支出单'},
                {id: 153403, name: '预收商品'},
                {id: 153407, name: '预收款单'},
                {id: 153406, name: '预付款单'},
                {id: 153404, name: '应收单'},
                {id: 153405, name: '应付单'},
            ])
        }
        if (cat === 'adminList') {
            sql = `select id,name from admin where tenantId=${tenantId} and isDel=0 `
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%' or mobile like '%${keyWord}%')`
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'accountList') {
            sql = `select id,code, name from category where pid in (SELECT id from category where tenantId=${tenantId} and typeNumber='subject' and name = N'库存现金')  and isDel=0  `;
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%')`
            }
            sql += ` union all  select id,code, name from category where pid in (SELECT id from category where tenantId=${tenantId} and typeNumber='subject' and name = N'银行存款') and isDel=0`;
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%')`;
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'ysSubject') {
            // 应收科目
            sql = `select id ,code,name from category  where tenantId=${tenantId} and typeNumber='subject' and  isDel=0 and name = N'其他应收款' `
            if (keyWord) {
                sql += ` and ( name like '%${keyWord}%' or code like '%${keyWord}%'  or pinYin like '%${keyWord}%' )`
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'yfSubject') {
            // 应付科目
            sql = `select id ,code,name from category  where tenantId=${tenantId} and typeNumber='subject' and  isDel=0 and name = N'其他应付款'`
            if (keyWord) {
                sql += ` and ( name like '%${keyWord}%' or code like '%${keyWord}%'  or pinYin like '%${keyWord}%' )`
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'subjectList') {
            sql = `select id, code,pid,iif(fullName='', name, fullName) as name from category where tenantId=${tenantId} and typeNumber='subject' and   isDel=0 `
            if (keyWord) {
                sql += ` and ( name like '%${keyWord}%' or code like '%${keyWord}%'  or pinYin like '%${keyWord}%' ) `
            }
            sql += " order by code asc";
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'inType') {
            return ctx.suc('', [
                {id: '盘盈', name: '盘盈'},{id: '其它', name: '其它'},
            ])
        }
        if (cat === 'outType') {
            return ctx.suc('', [
                {id: '盘亏', name: '盘亏'},
                {id: '报损', name: '报损'},
                {id: '其它', name: '其它'}
            ])
        }
        if (cat === 'incomeType') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0 and typeNumber='raccttype' ORDER BY code `;
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'outcomeType') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0 and typeNumber='paccttype' ORDER BY code `;
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'ystype') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0 and typeNumber='ystype' ORDER BY code `;
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'yftype') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0  and typeNumber='yftype' ORDER BY code `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'yfktype') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0  and typeNumber='yfktype' ORDER BY code `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'PayMethod') {
            sql = `select id,name from category where tenantId=${tenantId} and isDel=0 and typeNumber='PayMethod' `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'feeType') {
            sql = `select id,name,code,pid from category where tenantId=${tenantId} and isDel=0  and typeNumber='feeType' `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'location') {
            sql = `select id,name from storage where isDel=0 and tenantId=${tenantId} `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'unit') {
            sql = `select name as id,name from unit where isDel=0 order by sort `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'sex') {
            let list = [{
                id: '女',
                name: '女'
            },
                {
                    id: '男',
                    name: '男'
                },
            ]
            return ctx.suc('', list);
        }
        if (cat === 'billState') {
            let list = [{
                id: 'draft',
                name: '草稿'
            },
                {
                    id: 'accounted',
                    name: '已审核'
                },
                {
                    id: 'del',
                    name: '已删除'
                },
                {
                    id: 'all',
                    name: '全部'
                }
            ]
            return ctx.suc('', list);
        }
        if (cat === 'carList') {
            sql = `SELECT id,licence as name,load,length,width,height  from cars where tenantId=${tenantId} and isDel=0 `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'role') {
            sql = `select id,name as name from role where tenantId=${tenantId} `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'profitGroup') {
            return ctx.suc('',[
                {id: 'buId', name: '客户'},
                {id: 'invId', name: '商品'},
                {id: 'salesId', name: '业务员'},
                {id: 'billDate', name: '时间'},
            ])
        }
        if (cat === 'purGroupBy') {
            return ctx.suc('',[
                {id: 'buId', name: '供应商'},
                {id: 'invId', name: '商品'},
                {id: 'billDate', name: '时间'},
            ])
        }
        if (cat === 'direct') {
            let list = [{
                id: '借',
                name: '借'
            },
                {
                    id: '贷',
                    name: '贷'
                },
            ]
            return ctx.suc('', list);
        }
        if (cat === 'state') {
            let list = [{
                id: '正常',
                name: '正常'
            },
                {
                    id: '停用',
                    name: '停用'
                },
            ]
            return ctx.suc('', list);
        }
        if (cat === 'areaList') {
            sql = `SELECT * from area where tenantId in (SELECT tenantId from tenant_company where companyId=${tenantId}) `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'area') {
            sql = `SELECT * from area where tenantId=${tenantId} `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'zqList') {
            // 已有的账期列表
            sql = `SELECT id,ym name from zq where tenantId=${tenantId} order by id desc `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'srSubject') {
            // 收入科目
            sql = `select id,code,concat(code,name) as name from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'应收账款' union all 
                select id,code,concat(code,name) as name from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'营业外收入' 
                select id, code,concat(code,N'_其他业务收入_',name) as name from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0  
                and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务收入'  ) `
            if (keyWord) {
                sql += ` and (name like '%${keyWord}%' or code like '%${keyWord}%' or pinYin like '%${keyWord}%')`;
            }
            sql += " order by code asc";
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'zcSubject') {
            // 支出科目
            sql = `SELECT * from (
                select id,code,concat(code,name) name,pinYin,isDel from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'应付账款' union all  
                select id,code,concat(code,name) name,pinYin,isDel from category where tenantId=${tenantId} and typeNumber='subject' and  isDel=0   and name =N'营业外支出' union all  
                select id, code, concat(code,N'_销售费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'销售费用'  ) union all  
                select id, code, concat(code,N'_管理费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'管理费用'  ) union all   
                select id, code, concat(code,N'_财务费用_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'财务费用'  )  
                UNION ALL  select id, code, concat(code,N'_其他业务支出_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务支出'  )  
                UNION ALL select id, code, concat(code,N'_其他业务成本_',name) name,pinYin,isDel  from category where tenantId=${tenantId} and typeNumber='subject' and pid in (select id from category where typeNumber='subject' and tenantId=${tenantId} and name =N'其他业务成本') 
                 ) a where isDel=0 `
            if (keyWord) {
                sql += " and (name like '%${keyWord}%' or code like '%${keyWord}%' or pinYin like '%${keyWord}%')";
            }
            sql += " order by code asc";
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'goods_cat') {
            keyWord = keyWord.trim()
            sql = `select id,name,code,pid from goods_cat where tenantId=${tenantId} and isDel=0 and (name like '%${keyWord}%' or pinYin like '%${keyWord}%') `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'contactCat') {
            sql = `select id,name,code,pid from contact_cat where tenantId=${tenantId} and isDel=0 `
            if (keyWord) {
                sql += ` and name like '%${keyWord}%' `
            }
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'histPrice') {
            let user = ctx.user
            let body = ctx.request.body
            let tenantId = user.tenantId;
            let invId = body.invId;
            let unit = body.unit;
            let buId = tool.xe.toNumber(body.buId);
            let transType = tool.xe.toNumber(body.transType);
            if (!invId) {
                return ctx.err('')
            }
            try {
                let bu = buId ? ` and buId=${buId} ` :''
                let ui = unit ? ` and unit='${unit}' `:''
                let sql = `SELECT top 10 price from v_price_his_last
                             where tenantId=${tenantId} and transType=${transType} ${bu} ${ui}  and invId=${invId} order by id desc`;
                let priceHis = await db.query(sql, { type: QueryTypes.SELECT })
                if(priceHis.length === 0) {
                    let sqls = `SELECT top 10 price from v_price_his_last
                             where tenantId=${tenantId} and transType=${transType} ${ui}  and invId=${invId}  order by id desc`
                    priceHis = await db.query(sqls, { type: QueryTypes.SELECT })
                }
                let list = []
                priceHis = tool._.uniqBy(priceHis, 'price')
                for (let i = 0; i < priceHis.length; i++) {
                    list.push({id: i+1, name: priceHis[i].price, ext: '历史价格：'})
                }
                return ctx.suc('', list);
            } catch (e) {
                return ctx.err(e);
            }
        }
        if (cat === 'goodsCat') {
            if(id) {
                let ret = await db.models[cat].findOne({
                    where: {
                        tenantId,
                        code: id
                    }
                })
                return ctx.suc('', ret.name)
            }
            else {
                let user = ctx.user
                let tenantId = user.tenantId;
                let ret = await db.models['goodsCat'].findAll({
                    where: {
                        tenantId
                    },
                    attributes: ['id', 'code', 'name', 'pid']
                })
                let arr = []
                ret.map(row => {
                    arr.push({id: row.id, name: row.name, pid: row.pid, code: row.code})
                })
                let list = tools.toArrayTree(arr, )
                return ctx.suc('', list)
            }
        }
        if (cat === 'goodsCatPid') {
            let sql = `select id,name,pid from goods_cat where tenantId=${tenantId}`
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            let list = tool.toArrayTree(ret)
            return ctx.suc('', list)
        }
        if (cat === 'skCat') {
            let list = `select id,name from v_qtsr where tenantId=${tenantId} `
            if(keyWord) {
                list += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%' or code like '%${keyWord}%')  `
            }
            list += ' order by code asc '
            let ret = await db.query(list, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        if (cat === 'fkCat') {
            let list = `select id,name from v_qtzc where tenantId=${tenantId} `
            if(keyWord) {
                list += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%' or code like '%${keyWord}%')  `
            }
            list += ' order by code asc '
            let ret = await db.query(list, {type: QueryTypes.SELECT})
            return ctx.suc('', ret)
        }
        // 应付类别
        if (cat === 'yfList') {
            let sql = `select id,fullName as name from category where tenantId=${tenantId} and typeNumber='subject'
                         and  (code like '6602%'  or code like '6601%' or code like '6603%') `
            let list = await db.query(sql, {type:QueryTypes.SELECT})
            return ctx.suc('', list)
        }
        if (cat === 'brand') {
            let sql = `select id,name from goods_brand where tenantId=${tenantId} `
            let list = await db.query(sql, {type:QueryTypes.SELECT})
            return ctx.suc('', list)
        }
        // 应收类别
        if (cat === 'ysList') {
            let sql = `select id,fullName as name from category where tenantId=${tenantId} and typeNumber='subject'
                         and   code like '6051%' `
            let list = await db.query(sql, {type:QueryTypes.SELECT})
            return ctx.suc('', list)
        }
        if(cat === 'test') {
            let keyWord = ctx.request.body.keyWord
            let goods = `select id ,name from v_goods where tenantId=16 `
            if(keyWord) {
                goods += ` and (name like '%${keyWord}%' or pinYin like '%${keyWord}%') `
            }
            let ret = await db.query(goods, {type: QueryTypes.SELECT})
            ret.map(row => {
                row.suffix = 'xxx'
            })
            return ctx.suc('', ret)
        }
        let where = {  }
        if(!tenantId) {
            return ctx.err('请先登录')
        }
        if(tenantId) {
            where.tenantId = tenantId
        }
        if(id) {
            where.id = id
            let ret = await db.models[cat].findOne({
                where
            })
            return ctx.suc('', ret.name)
        }
        else {
            let attrs = db.models[cat].getAttributes()
            let or = {}
            for(let i in attrs) {
                if(i === 'isDel') {
                    where.isDel = 0
                }
                if(keyWord) {
                    if(i === 'pinYin') {
                        or.pinYin = {
                            [Op.like]: `%${keyWord}%`
                        }
                    }
                    if(i === 'name') {
                        or.name = {
                            [Op.like]: `%${keyWord}%`
                        }
                    }
                }
            }
            if(!tools.xe.isEmpty(or)) {
                where[Op.or] = or
            }
            let list = await db.models[cat].findAll({
                where ,
                attributes: ['id', 'name'],
                limit: 30
            })
            return ctx.suc('', list)
        }
    }
    catch (e) {
        return ctx.err(e)
    }
})
router.post('/dropdown/goodCatList', async ctx => {
    let body = ctx.request.body
    let user = ctx.user
    let list = await db.query(`select id,name label,pid,code from goods_cat where tenantId=${user.tenantId} `,{type: QueryTypes.SELECT})
    return ctx.suc('', list)
})
router.post('/dropdown/invList', async ctx => {
    let body = ctx.request.body
    let tenantId = ctx.user.tenantId
    // 弹出选择商品
    let goodsCatId = body.goodsCatId ? body.goodsCatId : ''
    let pageSize = body.pageSize ? body.pageSize : 30
    let currentPage = body.currentPage ? body.currentPage : 1
    let offset = (currentPage-1) * pageSize
    let goodsCode = ''
    if(goodsCatId) {
        let modcat = await db.models['goodsCat'].findOne({
            where: {
                tenantId,
                id: goodsCatId
            }
        })
        if(modcat) {
            goodsCode = modcat.code
        }
    }
    let codeStr = ''
    if(goodsCode) {
        codeStr = ` and gc.code like '${goodsCode}%' `
    }
    let checkedVal = body.checkedVal
    let keyWord = body.keyWord
    let buId = body.buId
    let transType = body.transType?body.transType:150601
    let str = keyWord ? ` and (a.name like '%${keyWord}%' or a.pinYin like '%${keyWord}%' or  a.barCode1 like '%${keyWord}%') ` : ''
    let buIdSql = body.buId ? ` and buId=${body.buId} `:''
    let cqs = `select * from contact_goods where tenantId=${tenantId} ${buIdSql}  `
    let stocksql = `select tenantId,invId,sum(lastCount) qty from inventory where tenantId=${tenantId} GROUP BY tenantId,invId`
    let sql = ''
    if(checkedVal === '近期交易') {
        sql = `select a.id,a.name,a.categoryId,a.spec,a.unit1,a.unit2,a.price1,a.price2,isnull(b.times,0) sort ,dbo.setAssQty(isnull(c.qty,0),a.spec,a.unit1,a.unit2) leftQty,gc.code goodsCode ,gc.name goodCatName 
                from v_goods a inner join (${cqs}) b on a.tenantId=b.tenantId and a.id=b.invId left join (${stocksql}) c on a.tenantId=c.tenantId and a.id=c.invId 
                left join goods_cat gc on a.tenantId=gc.tenantId and a.categoryId=gc.id 
                where a.tenantId=${tenantId} ${str} ${codeStr} `
    }
    else {
        sql = `select a.id,a.categoryId,a.name,a.spec,a.unit1,a.unit2,a.price1,a.price2,dbo.setAssQty(isnull(c.qty,0),a.spec,a.unit1,a.unit2) leftQty,gc.code goodsCode ,gc.name goodCatName
               from v_goods a  left join (${stocksql}) c on a.tenantId=c.tenantId and a.id=c.invId 
                left join goods_cat gc on a.tenantId=gc.tenantId and a.categoryId=gc.id 
                where a.tenantId=${tenantId} ${str} ${codeStr} `
    }

    let total = `select count(*) total from (${sql}) a `
    sql = `select *,unit1 unit from (${sql}) a order by categoryId,name offset ${offset} rows fetch next ${pageSize} rows only; `

    let ret = await db.query(sql, {type: QueryTypes.SELECT})
    let totalModel = await db.query(total, {type: QueryTypes.SELECT, plain: true})
    let ids = []
    ret.map(row => {
        ids.push(row.id)
    })
    if(ids.length > 0) {
        let buIdstr = ''
        if(buId) {
            buIdstr = ` and buId=${buId} `
        }
        let hist = `select invId,isnull(price,0) price,isnull(deduction,0) deduction from v_price_his_last where id in(select max(id) id from price_his where tenantId=${tenantId} and transType=${transType} 
         ${buIdstr} and isnull(price,0)<>0 and invId in(${ids.join(',')}))  `

        let histArr = await db.query(hist, {type: QueryTypes.SELECT})
        let stockSql = `select a.invId,round(a.lastCount/b.spec,2) leftQty from inventory a inner join v_goods b on a.tenantId=b.tenantId and a.invId=b.id where a.tenantId=${tenantId} and a.invId in(${ids.join(',')})`
        let stock = await db.query(stockSql, {type: QueryTypes.SELECT})

        ret.map(row => {
            row.suffix = tools.xe.toNumber(row.leftQty) === 0 ? '' : row.leftQty
            histArr.map(res => {
                if(row.id===res.invId) {
                    row.price = res.price
                    row.deduction = res.deduction
                }
            })
            stock.map(ret => {
                if(row.id===ret.invId) {
                    row.leftQty = ret.leftQty
                }
            })
        })
    }
    return ctx.suc('', {
        total: totalModel.total,
        list: ret
    })
})





module.exports = router;
